# -*- coding: utf-8 -*-
'''
@author: Eduardo Cardeira
@date: 30 de Outubro de 2012
@obs:
Este ficheiro contém a classe Read_And_Store, a qual é composta por métodos que
leêm o ficheiro 'Inscritos_2010-2011.xls', processam os dados e guardam-nos numa
base de dados em sqlite3.
'''


import xlrd
import sqlite3


class Read_And_Store:
    '''
    Classe com métodos que permitem a leitura do ficheiro xls e guardar os dados lidos numa base de dados.
    '''


    def __init__(self, database_name, table_name, workbook_name, worksheet_name):
        '''
        Método construtor da classe Read_And_Store.
        Inicializa as variáveis com nomes que irão ser utilizados por outros métodos da classe.
        database_name - nome da base de dados a criar/ligar
        table_name - nome da tabela a criar na base de dados
        workbook_name - nome do livro excel a ler
        worksheet_name - nome da folha a ler no livro excel indicado acima
        '''
        self.database_name = database_name
        self.table_name = table_name
        self.workbook_name = workbook_name
        self.worksheet_name = worksheet_name
        pass
    pass


    def connect_to_database(self):
        '''
        Método que permite a ligação/criação da base de dados.
        connection - ligação à base de dados que é devolvida
        '''
        try:
            connection = sqlite3.connect(self.database_name)
            print ('A ligação a ' + self.database_name + ' foi efetuada com sucesso!').decode('utf-8')
            return connection
        except IOError:
            print ('Erro: Não foi possível criar ou ligar a' + self.database_name).decode('utf-8')
        pass
    pass


    def open_xls_file(self):
        '''
        Método que permite abrir o livro Excel.
        xls_file - indicador para o livro excel aberto que é devolvido
        '''
        try:
            xls_file = xlrd.open_workbook(self.workbook_name)
            print (self.workbook_name + ' foi aberto com sucesso!').decode('utf-8')
            return xls_file
        except IOError:
            print ('Erro: Não foi possível abrir o ficheiro desejado!').decode('utf-8')
        pass
    pass


    def create_database_table(self):
        '''
        Método que cria a tabela onde vão ser guardados os dados na base de dados.
        '''
        try:
            connection = self.connect_to_database()

            cursor = connection.cursor()

            cursor.execute(('''DROP TABLE IF EXISTS "{0}"''').format(self.table_name))

            connection.commit()

            cursor.execute(('''CREATE TABLE IF NOT EXISTS "{0}" (Id INTEGER PRIMARY KEY,
                                                                Estabelecimento_de_Ensino TEXT,
                                                                Unidade_Organica TEXT,
                                                                Nivel_de_Formacao TEXT,
                                                                Curso_Superior TEXT,
                                                                Area_de_Educacao_e_Formacao TEXT,
                                                                Ano_95_96_H INTEGER,
                                                                Ano_95_96_M INTEGER,
                                                                Ano_95_96_HM INTEGER,
                                                                Ano_96_97_H INTEGER,
                                                                Ano_96_97_M INTEGER,
                                                                Ano_96_97_HM INTEGER,
                                                                Ano_97_98_H INTEGER,
                                                                Ano_97_98_M INTEGER,
                                                                Ano_97_98_HM INTEGER,
                                                                Ano_98_99_H INTEGER,
                                                                Ano_98_99_M INTEGER,
                                                                Ano_98_99_HM INTEGER,
                                                                Ano_99_00_H INTEGER,
                                                                Ano_99_00_M INTEGER,
                                                                Ano_99_00_HM INTEGER,
                                                                Ano_00_01_H INTEGER,
                                                                Ano_00_01_M INTEGER,
                                                                Ano_00_01_HM INTEGER,
                                                                Ano_01_02_H INTEGER,
                                                                Ano_01_02_M INTEGER,
                                                                Ano_01_02_HM INTEGER,
                                                                Ano_02_03_H INTEGER,
                                                                Ano_02_03_M INTEGER,
                                                                Ano_02_03_HM INTEGER,
                                                                Ano_03_04_H INTEGER,
                                                                Ano_03_04_M INTEGER,
                                                                Ano_03_04_HM INTEGER,
                                                                Ano_04_05_H INTEGER,
                                                                Ano_04_05_M INTEGER,
                                                                Ano_04_05_HM INTEGER,
                                                                Ano_05_06_H INTEGER,
                                                                Ano_05_06_M INTEGER,
                                                                Ano_05_06_HM INTEGER,
                                                                Ano_06_07_H INTEGER,
                                                                Ano_06_07_M INTEGER,
                                                                Ano_06_07_HM INTEGER,
                                                                Ano_07_08_H INTEGER,
                                                                Ano_07_08_M INTEGER,
                                                                Ano_07_08_HM INTEGER,
                                                                Ano_08_09_H INTEGER,
                                                                Ano_08_09_M INTEGER,
                                                                Ano_08_09_HM INTEGER,
                                                                Ano_09_10_H INTEGER,
                                                                Ano_09_10_M INTEGER,
                                                                Ano_09_10_HM INTEGER,
                                                                Ano_10_11_H INTEGER,
                                                                Ano_10_11_M INTEGER,
                                                                Ano_10_11_HM INTEGER)''').format(self.table_name))

            print ('A tabela "{0}" foi criada com sucesso!'.format(self.table_name)).decode('utf-8')

            connection.commit()
            
            connection.close()
        except IOError:
            print ('Erro: Não foi possível criar a tabela "{0}"!'.format(self.table_name)).decode('utf-8')
        pass
    pass


    def read_and_store_data(self):
        '''
        Método que lê o ficheiro xls e preenche a tabela previamente criada com os dados extraidos.
        '''
        try:
            connection = self.connect_to_database()

            cursor = connection.cursor()
            
            workbook = self.open_xls_file()

            worksheet = workbook.sheet_by_name(self.worksheet_name)

            Id = 0

            Estabelecimento_de_Ensino = ''

            Unidade_Organica = ''

            Nivel_de_Formacao = ''

            Curso_Superior = ''

            Area_de_Educacao_e_Formacao = ''

            # Esta lista vai conter os valores das várias células de cada linha.
            row_values = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                          0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]

            cmd = 'database command'

            print ('A ler e copiar informação. Por favor, aguarde...').decode('utf-8')

            for row in range(4, 11955):
    
                # Id
                Id += 1

                # Estabelecimento de Ensino
                if worksheet.cell_type(row, 0) != 0:
                    Estabelecimento_de_Ensino = str((worksheet.cell_value(row, 0)).encode('utf-8'))
                    pass
                pass

                # Unidade Organica
                if worksheet.cell_type(row, 1) != 0:
                    Unidade_Organica = str((worksheet.cell_value(row, 1)).encode('utf-8'))
                    pass
                pass

                # Nivel de Formacao
                if worksheet.cell_type(row, 2) != 0:
                    Nivel_de_Formacao = str((worksheet.cell_value(row, 2)).encode('utf-8'))
                    pass
                pass

                # Curso Superior
                if worksheet.cell_type(row, 3) != 0:
                    Curso_Superior = str((worksheet.cell_value(row, 3)).encode('utf-8'))
                    pass
                pass

                # Area de Educacao
                if worksheet.cell_type(row, 4) != 0:
                    Area_de_Educacao_e_Formacao = str((worksheet.cell_value(row, 4)).encode('utf-8'))
                    pass
                pass

                for cell in range(0, 48):
                    # Esta condição pula a coluna AA que não contém valores
                    if cell != 39:
                        # E esta converte os - para 0 quando os encontra
                        if worksheet.cell_value(row, (cell+5)) == '-':
                            row_values[cell] = 0
                        else:
                            row_values[cell] = int(worksheet.cell_value(row, (cell+5)))
                    pass
                pass

                cmd = '''INSERT INTO "{0}"(Id,
                                        Estabelecimento_de_Ensino,
                                        Unidade_Organica,
                                        Nivel_de_Formacao,
                                        Curso_Superior,
                                        Area_de_Educacao_e_Formacao,
                                        Ano_95_96_H,
                                        Ano_95_96_M,
                                        Ano_95_96_HM,
                                        Ano_96_97_H,
                                        Ano_96_97_M,
                                        Ano_96_97_HM,
                                        Ano_97_98_H,
                                        Ano_97_98_M,
                                        Ano_97_98_HM,
                                        Ano_98_99_H,
                                        Ano_98_99_M,
                                        Ano_98_99_HM,
                                        Ano_99_00_H,
                                        Ano_99_00_M,
                                        Ano_99_00_HM,
                                        Ano_00_01_H,
                                        Ano_00_01_M,
                                        Ano_00_01_HM,
                                        Ano_01_02_H,
                                        Ano_01_02_M,
                                        Ano_01_02_HM,
                                        Ano_02_03_H,
                                        Ano_02_03_M,
                                        Ano_02_03_HM,
                                        Ano_03_04_H,
                                        Ano_03_04_M,
                                        Ano_03_04_HM,
                                        Ano_04_05_H,
                                        Ano_04_05_M,
                                        Ano_04_05_HM,
                                        Ano_05_06_H,
                                        Ano_05_06_M,
                                        Ano_05_06_HM,
                                        Ano_06_07_H,
                                        Ano_06_07_M,
                                        Ano_06_07_HM,
                                        Ano_07_08_H,
                                        Ano_07_08_M,
                                        Ano_07_08_HM,
                                        Ano_08_09_H,
                                        Ano_08_09_M,
                                        Ano_08_09_HM,
                                        Ano_09_10_H,
                                        Ano_09_10_M,
                                        Ano_09_10_HM,
                                        Ano_10_11_H,
                                        Ano_10_11_M,
                                        Ano_10_11_HM)
                                        VALUES ("{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}", "{9}", "{10}",
                                                "{11}", "{12}", "{13}", "{14}", "{15}", "{16}", "{17}", "{18}", "{19}", "{20}",
                                                "{21}", "{22}", "{23}", "{24}", "{25}", "{26}", "{27}", "{28}", "{29}", "{30}",
                                                "{31}", "{32}", "{33}", "{34}", "{35}", "{36}", "{37}", "{38}", "{39}", "{40}",
                                                "{41}", "{42}", "{43}", "{44}", "{45}", "{46}", "{47}", "{48}", "{49}", "{50}",
                                                "{51}", "{52}", "{53}", "{54}")'''\
                                                .format(self.table_name, Id, Estabelecimento_de_Ensino,
                                                        Unidade_Organica, Nivel_de_Formacao, Curso_Superior,
                                                        Area_de_Educacao_e_Formacao, row_values[0], row_values[1],
                                                        row_values[2], row_values[3], row_values[4], row_values[5],
                                                        row_values[6], row_values[7], row_values[8], row_values[9],
                                                        row_values[10], row_values[11], row_values[12], row_values[13],
                                                        row_values[14], row_values[15], row_values[16], row_values[17],
                                                        row_values[18], row_values[19], row_values[20], row_values[21],
                                                        row_values[22], row_values[23], row_values[24], row_values[25],
                                                        row_values[26], row_values[27], row_values[28], row_values[29],
                                                        row_values[30], row_values[31], row_values[32], row_values[33],
                                                        row_values[34], row_values[35], row_values[36], row_values[37],
                                                        row_values[38], row_values[39], row_values[40], row_values[41],
                                                        row_values[42], row_values[43], row_values[44], row_values[45],
                                                        row_values[46], row_values[47])

                cursor.execute(cmd)
                pass
            
            connection.commit()
            connection.close()

            print ('Toda a informação foi copiada com sucesso para a base de dados!').decode('utf-8')
        except IOError:
            print ('Erro: Não foi possível copiar a informação para a base de dados!').decode('utf-8')
        pass
    pass


pass

